package com.ctshk.rpc.finance.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ctshk.rpc.finance.base.CustomBaseMapper;
import com.ctshk.rpc.finance.dto.bill.BillCheckDetailListDTO;
import com.ctshk.rpc.finance.dto.bill.CheckAmountStaticsDTO;
import com.ctshk.rpc.finance.entity.FinanceBill;
import com.ctshk.rpc.finance.entity.FinanceBillCheckDetail;
import com.ctshk.rpc.finance.req.bill.BillCheckDetailListReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 对账详细信息 Mapper 接口
 * </p>
 *
 * @author 叶冠峰
 * @since 2021-03-26
 */
public interface FinanceBillCheckDetailMapper extends CustomBaseMapper<FinanceBillCheckDetail> {

    /**
     * 币种金额统计
     * @param id
     * @return
     */
    @Select("SELECT currency_id,currency_name,exchange_rate,SUM(if(bill_type='1',total_amount,NULL)) AS collectAmount,SUM(if(bill_type='2',total_amount,NULL)) AS refundAmount\n" +
            "FROM t_finance_bill_check_detail\n" +
            "WHERE check_id = #{check_id} AND is_deleted = 0\n" +
            "GROUP BY currency_id")
    List<CheckAmountStaticsDTO> selectSumByCheck(@Param("check_id") Long id);

    /**
     * 批量删除
     * @param idList
     * @param userId
     * @return
     */
    @Update("<script>"+"UPDATE t_finance_bill_check_detail\n" +
            "SET is_deleted = 1,modified_id = #{user_id}\n" +
            "WHERE is_deleted = 0 AND check_id IN "+
            "<foreach collection=\"list\" item=\"id\" index=\"index\" open=\"(\" close=\")\" separator=\",\">\n" +
            "#{id}\n" +
            "</foreach>"+"</script>")
    Integer batchDel(@Param("list") List<Long> idList, @Param("user_id") Long userId);

    /**
     * 批量删除对账单明细
     * @param checkId
     * @param userId
     * @return
     */
    @Update("UPDATE t_finance_bill_check_detail\n" +
            "SET is_deleted = 1,modified_id = #{user_id}\n" +
            "WHERE is_deleted = 0 AND check_id = #{check_id}")
    Integer cleanByCheck(@Param("check_id")Long checkId, @Param("user_id") Long userId);

    /**
     * 往来明细列表
     * @param iPage
     * @param req
     * @return
     */
    @Select("<script>SELECT a.*,b.off_account_status,c.customer_name,c.check_number,c.approval_status \n" +
            "FROM t_finance_bill_check_detail a\n" +
            "LEFT JOIN t_finance_bill b ON a.bill_id = b.id\n" +
            "LEFT JOIN t_finance_bill_check c ON a.check_id = c.id\n" +
            "WHERE a.is_deleted = 0 \n"+
            "<if test=\"req.customerName != null and req.customerName != ''\">AND c.customer_name LIKE CONCAT('%',#{req.customerName},'%') </if> \n" +
            "<if test=\"req.createDeptId != null\">AND b.create_dept_id = #{req.createDeptId}</if> \n" +
            "<if test=\"req.createDateBegin != null and req.createDateEnd != null\">AND a.gmt_create BETWEEN #{req.createDateBegin} AND #{req.createDateEnd}</if> \n" +
            "<if test=\"req.saleUserId != null\">AND a.sale_user_id = #{req.saleUserId}</if> \n" +
            "<if test=\"req.productNumber != null and req.productNumber != ''\">AND a.product_number LIKE CONCAT('%',#{req.productNumber},'%') </if> \n" +
            "<if test=\"req.businessType != null and req.businessType.size() > 0\">AND a.business_type IN #{req.businessType}</if> \n" +
            "<if test=\"req.billType != null\">AND a.bill_type = #{req.billType}</if> \n" +
            "<if test=\"req.billNumber != null\">AND a.bill_number LIKE CONCAT('%',#{req.billNumber},'%') </if> \n" +
            "<if test=\"req.productName != null\">AND a.product_name LIKE CONCAT('%',#{req.productName},'%') </if> \n" +
            "<if test=\"req.checkStatus != null\">AND b.check_status = #{req.checkStatus}</if> \n" +
            "<if test=\"req.offAccountStatus != null\">AND b.off_account_status = #{req.offAccountStatus}</if> \n" +
            "ORDER BY a.gmt_create DESC "+
            "</script>")
    Page<BillCheckDetailListDTO> selectByCondition(@Param("iPage") Page<BillCheckDetailListDTO> iPage, @Param("req") BillCheckDetailListReq req);

    /**
     * 查询对账单关联的认款单ID列表
     * @param id
     * @return
     */
    @Select("SELECT b.*\n" +
            "FROM t_finance_bill_check_detail a \n" +
            "LEFT JOIN t_finance_bill b ON a.bill_id = b.id\n"+
            "WHERE a.is_deleted = 0 AND a.check_id = #{check_id}\n" +
            "GROUP BY a.bill_id")
    List<FinanceBill> queryBillIdByCheck(@Param("check_id") Long id);

    /**
     * 对账单详情-对账明细列表
     * @param iPage
     * @param checkId
     * @return
     */
    @Select("SELECT a.*,b.off_account_status,c.customer_name,c.check_number,c.approval_status \n" +
            "FROM t_finance_bill_check_detail a\n" +
            "LEFT JOIN t_finance_bill b ON a.bill_id = b.id\n" +
            "LEFT JOIN t_finance_bill_check c ON a.check_id = c.id\n" +
            "WHERE a.is_deleted = 0 AND a.check_id = #{check_id}")
    Page<BillCheckDetailListDTO> selectByCheck(@Param("iPage")Page<BillCheckDetailListDTO> iPage, @Param("check_id")Long checkId);

    /**
     * 根据billId查询
     * @param billId
     * @return
     */
    @Select("SELECT a.* \n" +
            "FROM t_finance_bill_check_detail a\n" +
            "LEFT JOIN t_finance_bill_check b ON a.check_id = b.id\n" +
            "WHERE a.bill_id = #{bill_id} AND a.is_deleted = 0 AND b.approval_status < 2 LIMIT 1")
    FinanceBillCheckDetail queryByBillId(@Param("bill_id") Long billId);
}
